﻿USE [ENNPassport]
GO
/****** Object:  Table [dbo].[Chatroom]    Script Date: 07/30/2010 16:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Chatroom](
	[ChatRoomId] [bigint] IDENTITY(1,1) NOT NULL,
	[EventId] [int] NOT NULL,
	[Title] [nvarchar](100) NULL,
	[CreateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Chatroom] PRIMARY KEY CLUSTERED 
(
	[ChatRoomId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ChatRoomMessages]    Script Date: 07/30/2010 16:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatRoomMessages](
	[MessageId] [bigint] IDENTITY(1,1) NOT NULL,
	[UserId] [bigint] NOT NULL,
	[ChatRoomId] [bigint] NOT NULL,
	[Message] [nvarchar](200) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ChatRoomMessages] PRIMARY KEY CLUSTERED 
(
	[MessageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[ChatRoom_GetList]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[ChatRoom_GetList]

AS
BEGIN
	Select *
	  From Chatroom
END
GO
/****** Object:  StoredProcedure [dbo].[ChatRoom_Add]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ChatRoom_Add]
@EventId int,
@Title nvarchar(100)
AS
BEGIN
	INSERT INTO [Chatroom]
		([EventId],[Title])
	VALUES
		(@EventId,@Title)
	Select SCOPE_IDENTITY() as 'ChatRoomId'
END
GO
/****** Object:  Table [dbo].[ChatRoomUsers]    Script Date: 07/30/2010 16:07:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatRoomUsers](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[ChatRoomId] [bigint] NOT NULL,
	[UserId] [bigint] NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[ReadOnly] [tinyint] NOT NULL,
 CONSTRAINT [PK_ChatRoomUsers] PRIMARY KEY CLUSTERED 
(
	[ChatRoomId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[ChatRoomMessages_GetListByChatRoomId]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ChatRoomMessages_GetListByChatRoomId]
@ChatRoomId bigint
,@LastMessageId bigint
AS
BEGIN
	Select *
	  From ChatRoomMessages
	Where ChatRoomId =@ChatRoomId and MessageId>@LastMessageId
	Order by MessageId asc
END
GO
/****** Object:  StoredProcedure [dbo].[ChatRoomMessages_Add]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[ChatRoomMessages_Add]
@ChatRoomId bigint,
@UserId bigint,
@Message nvarchar(200)
AS
BEGIN
	
	INSERT INTO dbo.ChatRoomMessages
		([ChatRoomId],[UserId],[Message])
	VALUES
		(@ChatRoomId,@UserId,@Message)
	Select SCOPE_IDENTITY() as 'id'
END
GO
/****** Object:  StoredProcedure [dbo].[ChatRoomUsers_GetListByChatRoomId]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ChatRoomUsers_GetListByChatRoomId]
@ChatRoomId bigint
AS
BEGIN
	Select *
	  From ChatRoomUsers
	Where ChatRoomId =@ChatRoomId 
	Order by Id asc
END
GO
/****** Object:  StoredProcedure [dbo].[ChatRoomUsers_Add]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ChatRoomUsers_Add]
@ChatRoomId bigint,
@UserId bigint,
@UserName nvarchar(50),
@ReadOnly tinyint
AS
BEGIN
	if exists(select userid from ChatRoomUsers where UserId=@UserId)
	Begin
		delete from ChatRoomUsers 
		where UserId = @UserId
	End
	INSERT INTO [ChatRoomUsers]
		([ChatRoomId],[UserId],[UserName],[ReadOnly])
	VALUES
		(@ChatRoomId,@UserId,@UserName,@ReadOnly)
	Select SCOPE_IDENTITY() as 'id'
END
GO
/****** Object:  StoredProcedure [dbo].[ChatRoomMessages_GetRoomAllListByUser]    Script Date: 07/30/2010 16:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[ChatRoomMessages_GetRoomAllListByUser]
@UserId bigint,
@LastMessageId bigint
AS
BEGIN
	Select *
	  From ChatRoomMessages 
	Where ChatRoomId in(select  ChatRoomId 
						  from dbo.ChatRoomUsers
						Where UserId=@UserId) 
		and MessageId>@LastMessageId
	Order by MessageId asc
END
GO
/****** Object:  Default [DF_ChatRoomUsers_ReadOnly]    Script Date: 07/30/2010 16:07:06 ******/
ALTER TABLE [dbo].[ChatRoomUsers] ADD  CONSTRAINT [DF_ChatRoomUsers_ReadOnly]  DEFAULT ((0)) FOR [ReadOnly]
GO
/****** Object:  Default [DF_ChatRoomMessages_CreateDate]    Script Date: 07/30/2010 16:07:06 ******/
ALTER TABLE [dbo].[ChatRoomMessages] ADD  CONSTRAINT [DF_ChatRoomMessages_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO
/****** Object:  Default [DF_Chatroom_EventId]    Script Date: 07/30/2010 16:07:06 ******/
ALTER TABLE [dbo].[Chatroom] ADD  CONSTRAINT [DF_Chatroom_EventId]  DEFAULT ((-1)) FOR [EventId]
GO
/****** Object:  Default [DF_Chatroom_CreateTime]    Script Date: 07/30/2010 16:07:06 ******/
ALTER TABLE [dbo].[Chatroom] ADD  CONSTRAINT [DF_Chatroom_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  ForeignKey [FK_ChatRoomUsers_ChatRoom]    Script Date: 07/30/2010 16:07:06 ******/
ALTER TABLE [dbo].[ChatRoomUsers]  WITH CHECK ADD  CONSTRAINT [FK_ChatRoomUsers_ChatRoom] FOREIGN KEY([ChatRoomId])
REFERENCES [dbo].[Chatroom] ([ChatRoomId])
GO
ALTER TABLE [dbo].[ChatRoomUsers] CHECK CONSTRAINT [FK_ChatRoomUsers_ChatRoom]
GO
/****** Object:  ForeignKey [FK_ChatRoom_ChatRoom]    Script Date: 07/30/2010 16:07:06 ******/
ALTER TABLE [dbo].[Chatroom]  WITH CHECK ADD  CONSTRAINT [FK_ChatRoom_ChatRoom] FOREIGN KEY([ChatRoomId])
REFERENCES [dbo].[Chatroom] ([ChatRoomId])
GO
ALTER TABLE [dbo].[Chatroom] CHECK CONSTRAINT [FK_ChatRoom_ChatRoom]
GO
